﻿/*
=============================================================================
Author:魏宝辉通过CodeSmithe生成
CreatedTime:2012-8-12
Description:RoomList三层结构数据访问层
=============================================================================
*/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using FTchina.Models;//引入模型层
namespace FTchina.DAL
{
	public static partial class RoomListService
	{
		#region 增加一条数据
		public static int AddRoomList(RoomList roomList)
		{
			string sql =
			"INSERT INTO RoomList(fanghao,leibie,louceng,danjia,kezhu,yizhu,shuoming,chuanghu,kongtiao,zhuangtai) "+
			"VALUES (@fanghao,@leibie,@louceng,@danjia,@kezhu,@yizhu,@shuoming,@chuanghu,@kongtiao,@zhuangtai)";
			
			 
				SqlParameter[] para = new SqlParameter[]
				{
						
					new SqlParameter("@fanghao",roomList.Fanghao),
						
					new SqlParameter("@leibie",roomList.Leibie),
						
					new SqlParameter("@louceng",roomList.Louceng),
						
					new SqlParameter("@danjia",roomList.Danjia),
						
					new SqlParameter("@kezhu",roomList.Kezhu),
						
					new SqlParameter("@yizhu",roomList.Yizhu),
						
					new SqlParameter("@shuoming",roomList.Shuoming),
						
					new SqlParameter("@chuanghu",roomList.Chuanghu),
						
					new SqlParameter("@kongtiao",roomList.Kongtiao),
						
					new SqlParameter("@zhuangtai",roomList.Zhuangtai)
				};
				int newId = DBHelper.ExecuteCommand(sql, para);
				return newId;
			 
		}
		#endregion
		#region 删除数据以主键，标识列，唯一列构建的删除
			
		public static int DeleteRoomListById(long id)	
		{
			string sql="DELETE "+"RoomList"+" Where id=@id";
			 SqlParameter[] para = new SqlParameter[]
				{
					new SqlParameter("@id", id)
				};
				return DBHelper.ExecuteCommand(sql, para);
			 
		}		
		
		#endregion
		#region 修改数据
		//考虑标识列与主键不一致情形
		//以标识列修改实体
		public static int ModifyRoomListByid(RoomList roomList)
		{
		string sql ="UPDATE "+"RoomList"+" set "+
					"fanghao=@fanghao, "+
					"leibie=@leibie, "+
					"louceng=@louceng, "+
					"danjia=@danjia, "+
					"kezhu=@kezhu, "+
					"yizhu=@yizhu, "+
					"shuoming=@shuoming, "+
					"chuanghu=@chuanghu, "+
					"kongtiao=@kongtiao, "+
					"zhuangtai=@zhuangtai "+ 
				" Where id=@id";
			 
				SqlParameter[] para = new SqlParameter[]
				{
					new SqlParameter("@id",roomList.Id),
					new SqlParameter("@fanghao",roomList.Fanghao),
					new SqlParameter("@leibie",roomList.Leibie),
					new SqlParameter("@louceng",roomList.Louceng),
					new SqlParameter("@danjia",roomList.Danjia),
					new SqlParameter("@kezhu",roomList.Kezhu),
					new SqlParameter("@yizhu",roomList.Yizhu),
					new SqlParameter("@shuoming",roomList.Shuoming),
					new SqlParameter("@chuanghu",roomList.Chuanghu),
					new SqlParameter("@kongtiao",roomList.Kongtiao),
					new SqlParameter("@zhuangtai",roomList.Zhuangtai)
				};
				return DBHelper.ExecuteCommand(sql, para);
			 
		}					
		#endregion
		#region 查询数据
		public static IList<RoomList> GetAllRoomLists()
		{
			string sqlAll = "SELECT * FROM RoomList";
			return GetRoomListsBySql(sqlAll);
		}
		//以标识列获得对象
		public static RoomList GetRoomListByid(object id)
		{
			string sql = "SELECT * FROM "+"RoomList"+" WHERE id = @id";
			
            using (SqlDataReader reader = DBHelper.GetReader(sql, new SqlParameter("@id", id)))
			{
				 
				if (reader.Read())
				{
					RoomList roomList = new RoomList();
					roomList.Id=Convert.ToInt32(reader["id"]);
					roomList.Fanghao=Convert.ToString(reader["fanghao"]);
					roomList.Leibie=Convert.ToString(reader["leibie"]);
					roomList.Louceng=Convert.ToString(reader["louceng"]);
					roomList.Danjia=Convert.ToString(reader["danjia"]);
					roomList.Kezhu=Convert.ToInt32(reader["kezhu"]);
					roomList.Yizhu=Convert.ToInt32(reader["yizhu"]);
					roomList.Shuoming=Convert.ToString(reader["shuoming"]);
					roomList.Chuanghu=Convert.ToString(reader["chuanghu"]);
					roomList.Kongtiao=Convert.ToString(reader["kongtiao"]);
					roomList.Zhuangtai=Convert.ToString(reader["zhuangtai"]);
					reader.Close();
					
					return roomList;
					}
				else
				{
					reader.Close();
					return null;
				}
			}
			 
		}
					
		//私有方法供构建好的SQL语句调用
		private static IList<RoomList> GetRoomListsBySql(string safeSql)
		{
			List<RoomList> list = new List<RoomList>();
			 
            using (DataTable table = DBHelper.GetDataTable(safeSql))
			{
				foreach (DataRow row in table.Rows)
				{
					RoomList roomList = new RoomList();
					roomList.Id=Convert.ToInt32(row["id"]);
					roomList.Fanghao=Convert.ToString(row["fanghao"]);
					roomList.Leibie=Convert.ToString(row["leibie"]);
					roomList.Louceng=Convert.ToString(row["louceng"]);
					roomList.Danjia=Convert.ToString(row["danjia"]);
					roomList.Kezhu=Convert.ToInt32(row["kezhu"]);
					roomList.Yizhu=Convert.ToInt32(row["yizhu"]);
					roomList.Shuoming=Convert.ToString(row["shuoming"]);
					roomList.Chuanghu=Convert.ToString(row["chuanghu"]);
					roomList.Kongtiao=Convert.ToString(row["kongtiao"]);
					roomList.Zhuangtai=Convert.ToString(row["zhuangtai"]);
					list.Add(roomList);
				}
				return list;
			}
             
		}
		private static IList<RoomList> GetRoomListsBySql( string sql, params SqlParameter[] values )
		{
			List<RoomList> list = new List<RoomList>();
			 
            using (DataTable table = DBHelper.GetDataTable(sql, values))
			{
				foreach (DataRow row in table.Rows)
				{
					RoomList roomList = new RoomList();
					roomList.Id=Convert.ToInt32(row["id"]);
					roomList.Fanghao=Convert.ToString(row["fanghao"]);
					roomList.Leibie=Convert.ToString(row["leibie"]);
					roomList.Louceng=Convert.ToString(row["louceng"]);
					roomList.Danjia=Convert.ToString(row["danjia"]);
					roomList.Kezhu=Convert.ToInt32(row["kezhu"]);
					roomList.Yizhu=Convert.ToInt32(row["yizhu"]);
					roomList.Shuoming=Convert.ToString(row["shuoming"]);
					roomList.Chuanghu=Convert.ToString(row["chuanghu"]);
					roomList.Kongtiao=Convert.ToString(row["kongtiao"]);
					roomList.Zhuangtai=Convert.ToString(row["zhuangtai"]);
					list.Add(roomList);
				}
				return list;
			}
            
			 
		}
		#endregion
		#region 分页的方法page为当前页码，NUMCount为每页数据
		public static IList<RoomList> ShowRoomListForPage(int curragePage,int NUMCount)
		{
			int number = 0;
			if (curragePage > 1) {
				number = NUMCount * (curragePage - 1);
			}
			string strsql= "select top "
						   +NUMCount+" * from RoomList where  id not in ( select top "
						   +number+" id from RoomList order by id desc ) order by id desc";
			return GetRoomListsBySql(strsql);
		}
		#endregion
	}
}

